package me.zhengjie.business.service.impl;

import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import me.zhengjie.business.domain.vo.KCVo;
import me.zhengjie.business.domain.vo.Kc08Vo;
import me.zhengjie.business.domain.vo.PersonFeeVo;
import me.zhengjie.business.domain.vo.PersonVo;
import me.zhengjie.business.service.Kc08Service;
import me.zhengjie.business.service.dto.Kc08QueryCriteria;
import me.zhengjie.utils.PageUtil;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author lyl
 * @since 2023/11/8
 */
@Slf4j
@Service
@RequiredArgsConstructor
public class Kc08ServiceImpl implements Kc08Service {

    @PersistenceContext
    private EntityManager em;

    @Override
    public Map<String, Object> queryKc08VoPageList(Kc08QueryCriteria criteria, int[] startEnd) {
        String tempSql = "select " +
                "       t.psn_no, " +
                "       t.psn_name, " +
                "       t.psn_certno, " +
                "       t.aac001, " +
                "       t.insutype, " +
                "       t.insutype_name, " +
                "       t.emp_name, " +
                "       t.insu_admdvs, " +
                "       t.paus_type, " +
                "       t.paus_type_name, " +
                "       t.chg_rea_dscr, " +
                "       t.begndate, " +
                "       t.enddate " +
                "  from KC08 t  " +
                "  where 1=1";

        String conditionSql1 = "";
        if(!StringUtils.isEmpty(criteria.getXm())) {
            conditionSql1 += " and t.psn_name like :xm ";
        }
        if(!StringUtils.isEmpty(criteria.getSfz())) {
            conditionSql1 += " and t.psn_certno like :sfz ";
        }

        tempSql += conditionSql1;

        Query query = em.createNativeQuery(tempSql);
        query.setFirstResult(startEnd[0]);
        query.setMaxResults(startEnd[1] - startEnd[0]);

        if(!StringUtils.isEmpty(criteria.getXm())) {
            query.setParameter("xm", "%" + criteria.getXm() + "%");
        }
        if(!StringUtils.isEmpty(criteria.getSfz())) {
            query.setParameter("sfz", "%" + criteria.getSfz() + "%");
        }

        List result = query.getResultList();
        List<Kc08Vo> kc08VoList = new ArrayList<>();
        for (Object o : result) {
            Object[] arr = (Object[]) o;
            Kc08Vo vo = new Kc08Vo();
            vo.setPsnNo(arr[0]== null ? "" :arr[0].toString());
            vo.setPsnName(arr[1]== null ? "" :arr[1].toString());
            vo.setPsnCertno(arr[2]== null ? "" :arr[2].toString());
            vo.setAac001(arr[3]== null ? "" :arr[3].toString());
            vo.setInsutype(arr[4]== null ? "" :arr[4].toString());
            vo.setInsutypeName(arr[5]== null ? "" :arr[5].toString());
            vo.setEmpName(arr[6]== null ? "" :arr[6].toString());
            vo.setInsuAdmdvs(arr[7]== null ? "" :arr[7].toString());
            vo.setPausType(arr[8]== null ? "" :arr[8].toString());
            vo.setPausTypeName(arr[9]== null ? "" :arr[9].toString());
            vo.setChgReaDscr(arr[10]== null ? "" :arr[10].toString());
            vo.setBegndate((Date) arr[11]);
            vo.setEnddate((Date) arr[12]);
            kc08VoList.add(vo);
        }
        String countSql = "select count(1) from ( " + tempSql + "  ) n where 1=1 ";
        String conditionSql2 = "";
        if(!StringUtils.isEmpty(criteria.getXm())) {
            conditionSql2 += " and n.psn_name like :xm ";
        }
        if(!StringUtils.isEmpty(criteria.getSfz())) {
            conditionSql2 += " and n.psn_certno like :sfz ";
        }

        countSql += conditionSql2;
        Query queryCount = em.createNativeQuery(countSql);

        if(!StringUtils.isEmpty(criteria.getXm())) {
            queryCount.setParameter("xm", "%" + criteria.getXm() + "%");
        }
        if(!StringUtils.isEmpty(criteria.getSfz())) {
            queryCount.setParameter("sfz", "%" + criteria.getSfz() + "%");
        }
        Object totalElements = queryCount.getSingleResult();
        Map<String,Object> m = PageUtil.toPage(kc08VoList, totalElements);
        return m;
    }

    @Override
    public Map<String, Object> querydetailFee(Kc08QueryCriteria criteria, int[] startEnd) {
        String tempSql = "select t.aac001," +
                "       t.aac003," +
                "       t.aac002," +
                "       t.accrym_begn," +
                "       t.accrym_end," +
                "       t.insutype," +
                "       t.clct_clag" +
                "  from VIEW_PERSON_FEE t" +
                " where 1 = 1 and  t.clct_clag = 1";

        String conditionSql1 = "";
        if(!StringUtils.isEmpty(criteria.getXm())) {
            conditionSql1 += " and t.aac003 like :xm ";
        }
        if(!StringUtils.isEmpty(criteria.getSfz())) {
            conditionSql1 += " and t.aac002 like :sfz ";
        }

        tempSql += conditionSql1 + " order by accrym_begn desc";

        Query query = em.createNativeQuery(tempSql);
        query.setFirstResult(startEnd[0]);
        query.setMaxResults(startEnd[1] - startEnd[0]);

        if(!StringUtils.isEmpty(criteria.getXm())) {
            query.setParameter("xm", "%" + criteria.getXm() + "%");
        }
        if(!StringUtils.isEmpty(criteria.getSfz())) {
            query.setParameter("sfz", "%" + criteria.getSfz() + "%");
        }

        List result = query.getResultList();
        List<PersonFeeVo> personFeeVoList = new ArrayList<>();
        for (Object o : result) {
            Object[] arr = (Object[]) o;
            PersonFeeVo vo = new PersonFeeVo();
            vo.setAac001(arr[0]== null ? "" :arr[0].toString());
            vo.setAac003(arr[1]== null ? "" :arr[1].toString());
            vo.setAac002(arr[2]== null ? "" :arr[2].toString());
            vo.setAccrymBegn(arr[3]== null ? "" :arr[3].toString());
            vo.setAccrymEnd(arr[4]== null ? "" :arr[4].toString());
            vo.setInsutype(arr[5]== null ? "" :arr[5].toString());
            vo.setClctClag(arr[6]== null ? "" :arr[6].toString());
            personFeeVoList.add(vo);
        }
        String countSql = "select count(1) from ( " + tempSql + "  ) n where 1=1 ";
        String conditionSql2 = "";
        if(!StringUtils.isEmpty(criteria.getXm())) {
            conditionSql2 += " and n.aac003 like :xm ";
        }
        if(!StringUtils.isEmpty(criteria.getSfz())) {
            conditionSql2 += " and n.aac002 like :sfz ";
        }

        countSql += conditionSql2;
        Query queryCount = em.createNativeQuery(countSql);

        if(!StringUtils.isEmpty(criteria.getXm())) {
            queryCount.setParameter("xm", "%" + criteria.getXm() + "%");
        }
        if(!StringUtils.isEmpty(criteria.getSfz())) {
            queryCount.setParameter("sfz", "%" + criteria.getSfz() + "%");
        }
        Object totalElements = queryCount.getSingleResult();
        Map<String,Object> m = PageUtil.toPage(personFeeVoList, totalElements);
        return m;
    }

    @Override
    public Map<String, Object> queryRyxx(Kc08QueryCriteria criteria, int[] startEnd) {
        String tempSql = "select " +
                "       t.aab034_name," +
                "       t.aac001," +
                "       t.aac003," +
                "       t.aac002," +
                "       t.aac008," +
                "       t.akc021," +
                "       t.cardno," +
                "       t.aac031," +
                "       t.aab034_code," +
                "       t.aac008_code," +
                "       t.akc021_code," +
                "       t.aac031_code" +
                "  from VIEW_PERSON t" +
                " where 1 = 1";

        String conditionSql1 = "";
        if(!StringUtils.isEmpty(criteria.getXm())) {
            conditionSql1 += " and t.aac003 like :xm ";
        }
        if(!StringUtils.isEmpty(criteria.getSfz())) {
            conditionSql1 += " and t.aac002 like :sfz ";
        }

        tempSql += conditionSql1;

        Query query = em.createNativeQuery(tempSql);
        query.setFirstResult(startEnd[0]);
        query.setMaxResults(startEnd[1] - startEnd[0]);

        if(!StringUtils.isEmpty(criteria.getXm())) {
            query.setParameter("xm", "%" + criteria.getXm() + "%");
        }
        if(!StringUtils.isEmpty(criteria.getSfz())) {
            query.setParameter("sfz", "%" + criteria.getSfz() + "%");
        }

        List result = query.getResultList();
        List<PersonVo> personVoList = new ArrayList<>();
        for (Object o : result) {
            Object[] arr = (Object[]) o;
            PersonVo vo = new PersonVo();
            vo.setAab034Name(arr[0].toString());
            vo.setAac001(arr[1] == null ? "" :arr[1].toString());
            vo.setAac003(arr[2] == null ? "" :arr[2].toString());
            vo.setAac002(arr[3] == null ? "" :arr[3].toString());
            vo.setAac008(arr[4] == null ? "" :arr[4].toString());
            vo.setAkc021(arr[5] == null ? "" :arr[5].toString());
            vo.setCardno(arr[6] == null ? "" :arr[6].toString());
            vo.setAac031(arr[7] == null ? "" :arr[7].toString());
            vo.setAab034Code(arr[8]== null ? "" :arr[8].toString());
            vo.setAac008Code(arr[9]== null ? "" :arr[9].toString());
            vo.setAkc021Code(arr[10]== null ? "" :arr[10].toString());
            vo.setAac031Code(arr[11]== null ? "" :arr[11].toString());
            personVoList.add(vo);
        }
        String countSql = "select count(1) from ( " + tempSql + "  ) n where 1=1 ";
        String conditionSql2 = "";
        if(!StringUtils.isEmpty(criteria.getXm())) {
            conditionSql2 += " and n.aac003 like :xm ";
        }
        if(!StringUtils.isEmpty(criteria.getSfz())) {
            conditionSql2 += " and n.aac002 like :sfz ";
        }

        countSql += conditionSql2;
        Query queryCount = em.createNativeQuery(countSql);

        if(!StringUtils.isEmpty(criteria.getXm())) {
            queryCount.setParameter("xm", "%" + criteria.getXm() + "%");
        }
        if(!StringUtils.isEmpty(criteria.getSfz())) {
            queryCount.setParameter("sfz", "%" + criteria.getSfz() + "%");
        }
        Object totalElements = queryCount.getSingleResult();
        Map<String,Object> m = PageUtil.toPage(personVoList, totalElements);
        return m;
    }

    @Override
    public Map<String, Object> queryZhbxxx(Kc08QueryCriteria criteria, int[] startEnd) {
        String tempSql = " select " +
                "  kc20.aac001," +
                "  kc20.aac003," +
                "  kc20.aab004," +
                "  kc20.akc170," +
                "  kc20.akb020," +
                "  kc20.akb021," +
                "  kc20.aae030," +
                "  kc20.aae031," +
                "  kc20.akc175," +
                "  kc20.aae100," +
                "  kc20.aae073," +
                "  kc55.aac002," +
                "  kc55.akc551," +
                "  kc55.aae006," +
                "  kc55.akc552," +
                "  kc55.aae005," +
                "  kc55.akc553," +
                "  kc55.akc554," +
                "  kc55.akc555," +
                "  kc55.aka120," +
                "  kc55.akc501," +
                "  kc55.akc502," +
                "  kc55.akc503," +
                "  kc55.akc504," +
                "  kc55.akc505," +
                "  kc55.akc506," +
                "  kc55.akc507," +
                "  kc55.akc508," +
                "  kc55.akc509," +
                "  kc55.akc510," +
                "  kc55.akc511," +
                "  kc55.akc512," +
                "  kc55.akc513," +
                "  kc55.aae036," +
                "  kc55.aae011," +
                "  kc55.akc514" +
                "  from kc20, kc55" +
                " where kc20.aae073 = kc55.aae073" +
                " and kc20.aae100 = 1 ";

        String conditionSql1 = "";
        if(!StringUtils.isEmpty(criteria.getXm())) {
            conditionSql1 += " and kc20.aac003 like :xm ";
        }
        if(!StringUtils.isEmpty(criteria.getSfz())) {
            conditionSql1 += " and kc55.aac002 like :sfz ";
        }

        tempSql += conditionSql1;

        Query query = em.createNativeQuery(tempSql);
        query.setFirstResult(startEnd[0]);
        query.setMaxResults(startEnd[1] - startEnd[0]);

        if(!StringUtils.isEmpty(criteria.getXm())) {
            query.setParameter("xm", "%" + criteria.getXm() + "%");
        }
        if(!StringUtils.isEmpty(criteria.getSfz())) {
            query.setParameter("sfz", "%" + criteria.getSfz() + "%");
        }

        List result = query.getResultList();
        List<KCVo> kcVoList = new ArrayList<>();
        for (Object o : result) {
            Object[] arr = (Object[]) o;
            KCVo vo = new KCVo();
            vo.setAac001(arr[0]== null ? "" :arr[0].toString());
            vo.setAac003(arr[1]== null ? "" :arr[1].toString());
            vo.setAab004(arr[2]== null ? "" :arr[2].toString());
            vo.setAkc170(arr[3]== null ? "" :arr[3].toString());
            vo.setAkb020(arr[4]== null ? "" :arr[4].toString());
            vo.setAkb021(arr[5]== null ? "" :arr[5].toString());
            vo.setAae030(arr[6]== null ? "" :arr[6].toString());
            vo.setAae031(arr[7]== null ? "" :arr[7].toString());
            vo.setAkc175(arr[8]== null ? "" :arr[8].toString());
            vo.setAae100(arr[9]== null ? "" :arr[9].toString());
            vo.setAae073(arr[10] == null ? "" :arr[10].toString());
            vo.setAac002(arr[11] == null ? "" :arr[11].toString());
            vo.setAkc551(arr[12] == null ? "" :arr[12].toString());
            vo.setAae006(arr[13] == null ? "" :arr[13].toString());
            vo.setAkc552(arr[14] == null ? "" :arr[14].toString());
            vo.setAae005(arr[15] == null ? "" :arr[15].toString());
            vo.setAkc553(arr[16] == null ? "" :arr[16].toString());
            vo.setAkc554(arr[17] == null ? "" :arr[17].toString());
            vo.setAkc555(arr[18] == null ? "" :arr[18].toString());
            vo.setAka120(arr[19] == null ? "" :arr[19].toString());
            vo.setAkc501(arr[20] == null ? "" :arr[20].toString());
            vo.setAkc502(arr[21] == null ? "" :arr[21].toString());
            vo.setAkc503(arr[22] == null ? "" :arr[22].toString());
            vo.setAkc504(arr[23] == null ? "" :arr[23].toString());
            vo.setAkc505(arr[24] == null ? "" :arr[24].toString());
            vo.setAkc506(arr[25] == null ? "" :arr[25].toString());
            vo.setAkc507(arr[26] == null ? "" :arr[26].toString());
            vo.setAkc508(arr[27] == null ? "" :arr[27].toString());
            vo.setAkc509(arr[28] == null ? "" :arr[28].toString());
            vo.setAkc510(arr[29] == null ? "" :arr[29].toString());
            vo.setAkc511(arr[30] == null ? "" :arr[30].toString());
            vo.setAkc512(arr[31] == null ? "" :arr[31].toString());
            vo.setAkc513((Date) arr[32]);
            vo.setAae036((Date) arr[33]);
            vo.setAae011(arr[34] == null ? "" :arr[34].toString());
            vo.setAkc514(arr[35] == null ? "" :arr[35].toString());
            kcVoList.add(vo);
        }
        String countSql = "select count(1) from ( " + tempSql + "  ) n where 1=1 ";
        String conditionSql2 = "";
        if(!StringUtils.isEmpty(criteria.getXm())) {
            conditionSql2 += " and n.aac003 like :xm ";
        }
        if(!StringUtils.isEmpty(criteria.getSfz())) {
            conditionSql2 += " and n.aac002 like :sfz ";
        }

        countSql += conditionSql2;
        Query queryCount = em.createNativeQuery(countSql);

        if(!StringUtils.isEmpty(criteria.getXm())) {
            queryCount.setParameter("xm", "%" + criteria.getXm() + "%");
        }
        if(!StringUtils.isEmpty(criteria.getSfz())) {
            queryCount.setParameter("sfz", "%" + criteria.getSfz() + "%");
        }
        Object totalElements = queryCount.getSingleResult();
        Map<String,Object> m = PageUtil.toPage(kcVoList, totalElements);
        return m;
    }
}
